/**
 * 文件名称:  CarBaseCountServiceImpl.java
 * 文件序号:  序号
 * 内容描述:  文件简单描述，多行换行书写
 * 版权声明:  山东鸿蝶信息技术有限公司
 * 创建用户:  zhangkai
 * 创建日期:  2017/2/25 14:05
 * 修改用户:  无
 * 修改日期:  无
 * 修改说明:  无
 * 文件版本:  V1.0
 * 文件路径:
 */

package com.hongdee.crm.task.service.impl;


import com.hongdee.annotation.MethodComment;
import com.hongdee.common.util.DateUtils;
import com.hongdee.crm.base.dao.ICarDao;
import com.hongdee.crm.task.service.ICarBaseCountService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

/**
 * 名  称:CarBaseCountServiceImpl
 * 类  型: class
 * 描  述: 用一句话描述类的作用
 * 路  径:com.hongdee.crm.base.service.impl
 * 作  者:zhangkai
 * 日  期:2017/2/25 14:05
 */
@Service
public class CarBaseCountServiceImpl implements ICarBaseCountService {
    private Logger logger = LoggerFactory.getLogger(CarBaseCountServiceImpl.class);
    @Autowired
    private ICarDao carDao;

    /**
     * 计算基盘车辆
     *
     * @throws Exception
     */
    @Override
    @MethodComment(name = "计算基盘新车增长")
    public synchronized Integer countBaseCarQuartz() throws Exception {
        /**
         * 1.取得全部工单计算车辆
         * 2.工单对应车辆本次进厂日期，上次进厂日期
         * 3.前次有效进厂工单所对应车辆为本店购车的标记为 新车增长，否则标记为社会招揽，工单结算日期为标记日期
         * 4.工单进厂日期对差为>12个月的标记为流失返还
         */
        //取得和公司自定义的流失月数
        String lsMonth = "select * from t_bc_param_set where code = 'lsys' ";
        List<Map<String, Object>> lsParams = this.carDao.list(lsMonth);

        //首次进厂
        logger.info("查询" + DateUtils.getTimeStr() + "计算进厂工单");
        // 清除历史数据
        String delSQL = " delete from  t_crm_car_base_detail";
        this.carDao.executeSQL(delSQL);

        //计算基盘车辆
        StringBuffer sb = new StringBuffer();
        //sb.append(" INSERT INTO t_crm_car_base_detail(id, car_id, plate_num,vin,tag_date, base_type)                                         ")
        sb.append(" INSERT INTO t_crm_car_base_detail(id,company_id, car_id, plate_num,vin, tag_date, base_type) SELECT uuid(),wo.company_id, wo.car_id, wo.plate_num,wo.vin,wo.enterDate, CASE WHEN wo.preEnterDate IS NOT NULL THEN 2 ELSE (  ")
                .append("        SELECT CASE WHEN c.from_company = '是' THEN 1 ELSE 3 END  ")
                .append("        FROM t_crm_car c  ")
                .append("         WHERE c.id = wo.car_id  ")
                .append("    ) END AS newCar  ")
                .append("    FROM (SELECT DISTINCT o1.company_id,o1.car_id, FROM_UNIXTIME( (o1.balance_date / 1000), '%Y-%m-%d') AS enterDate, (  ")
                .append("                  SELECT MAX(FROM_UNIXTIME( (o2.balance_date / 1000), '%Y-%m-%d'))  ")
                .append("          FROM t_crm_car_workorder o2  ")
                .append("          WHERE o2.real_amount  > '0.00'  ")
                .append("    AND FROM_UNIXTIME( (o2.balance_date / 1000), '%Y-%m-%d') < FROM_UNIXTIME( (o1.balance_date / 1000), '%Y-%m-%d')  ")
                .append("    AND o1.car_id = o2.car_id  ")
                .append("  	) AS preEnterDate,o1.plate_num,o1.vin  ")
                .append("   FROM t_crm_car_workorder o1  ")
                .append("   WHERE o1.real_amount > '0.00'  ")
                .append("     AND o1.car_id IS NOT NULL  ")
                .append("  ) wo  ")
                .append("     WHERE wo.preEnterDate IS NULL  ")
                //暂定一年
                .append("    OR DATE_ADD(wo.preEnterDate,INTERVAL 1 YEAR) < wo.enterDate  ");

        //生成数据
        //this.carDao.list(sb.toString());
        int result = this.carDao.executeSQL(sb.toString());
        logger.info("生成基盘车辆：" + result);
        return result;

    }

    /***
     * 计算流失车辆
     * @throws Exception
     */
    @Override
    @MethodComment(name = "计算车辆流失明细")
    public synchronized Integer countLostCarQuartz() throws Exception {


        StringBuffer sb = new StringBuffer();
        sb.append(" INSERT INTO t_crm_car_lost_detail(id,company_id,car_id ,start_date,end_date) ");
        sb.append(" SELECT uuid(),wo.company_id, ")
                .append("     wo.car_id, ")
                .append("       DATE_ADD(wo.enterDate, INTERVAL 1 YEAR) as enterDate, ")
                .append("  wo.nextEnterDAte ")
                .append("   FROM( ")
                .append("           SELECT DISTINCT o1.company_id, o1.car_id, FROM_UNIXTIME(o1.balance_date/1000, '%Y-%m-%d') AS enterDate,( ")
                .append("                SELECT MIN(FROM_UNIXTIME(o2.balance_date/1000, '%Y-%m-%d')) ")
                .append("       FROM t_crm_car_workorder o2 ")
                .append("     WHERE o2.real_amount> '0.00' ")
                .append("    AND FROM_UNIXTIME(o2.balance_date/1000, '%Y-%m-%d')> FROM_UNIXTIME(o1.balance_date/1000, '%Y-%m-%d') ")
                .append("    AND o1.car_id= o2.car_id) AS nextEnterDate ")
                .append("  FROM t_crm_car_workorder o1 ")
                .append("  WHERE o1.real_amount> '0.00' ")
                .append("  AND o1.car_id IS NOT NULL) wo ")

                .append("  WHERE DATE_ADD(wo.enterDate, INTERVAL 1 YEAR) < case when wo.nextEnterDate is null then DATE_FORMAT(now(),'%Y-%m-%d') else wo.nextEnterDate end ");


        int delCount = this.carDao.executeSQL(" delete from  t_crm_car_lost_detail ");
        logger.info("删除旧的流失车辆：" + String.valueOf(delCount) + "辆");

        int newCount = this.carDao.executeSQL(sb.toString());
        logger.info("重新计算流失车辆：" + newCount + "辆");
        //更新车辆vin 车牌号
        String sql="update t_crm_car_lost_detail detail,t_crm_car car set detail.plate_num=car.plate_num ,detail.vin=car.vin where detail.company_id=car.company_id and detail.car_id=car.id";
        this.carDao.executeSQL(sql);
        return newCount;
    }

    /**
     * 计算车龄
     *
     * @throws Exception
     */
    @Override
    @MethodComment(name = "计算车辆车龄")
    public Integer countCarAgeQuartz() throws Exception {
        logger.info("系统于" + DateUtils.getTimeStr() + "计算车龄");
        int count = 0;
        String sql = " update t_crm_car c set c.car_age =  ceil(TIMESTAMPDIFF(DAY," +
                " FROM_UNIXTIME(c.guarantee_start_date /1000), NOW())/365)";
        count += this.carDao.executeSQL(sql);

        String sqllicenceDate = " update t_crm_car c set c.car_age =  ceil(TIMESTAMPDIFF(DAY,  FROM_UNIXTIME(c.licence_date /1000), NOW())/365) where car_age is null and c.guarantee_start_date is null";
        count += this.carDao.executeSQL(sqllicenceDate);


        String sqlBuyDate = " update t_crm_car c set c.car_age =  ceil(TIMESTAMPDIFF(DAY,  FROM_UNIXTIME(c.buy_date /1000), NOW())/365) where car_age is null and c.guarantee_start_date is null and c.licence_date is null";
        count += this.carDao.executeSQL(sqlBuyDate);
        logger.info("系统于" + DateUtils.getTimeStr() + "成功计算" + count + "辆车车龄");

        //无质保取购买日期，无购买取上牌
        sql = "update t_crm_car c set c.guarantee_start_date = case when c.buy_date is not null then c.buy_date else c.licence_date end where c.guarantee_start_date is null";
        this.carDao.executeSQL(sql);
        //无购买取上牌，无上牌取质保
        sql = "update t_crm_car c set c.buy_date = case when c.buy_date is not null then c.guarantee_start_date else c.licence_date end where c.buy_date is null";
        this.carDao.executeSQL(sql);
        //无上牌就空着


        //增加车型
        return count;

    }

    /**
     * 向延保刷记录表中刷入车辆车型,车辆 ID
     *
     * @throws Exception
     */
    @Override
    @MethodComment(name = "刷新延保记录车辆ID及车型")
    public Integer updateExCarSeriesQuartz() throws Exception {
        logger.info("系统于" + DateUtils.getTimeStr() + "刷新延保记录中车型");
        String sql = " update t_crm_car_exsurance  ex inner join t_crm_Car c on ex.vin = c.vin  set  ex.car_series = c.series,ex.user_carid = c.id  where ( ex.user_carid is null or ex.user_carid ='' ) ";
        int count = this.carDao.executeSQL(sql);
        logger.info("系统于" + DateUtils.getTimeStr() + "刷新延保记录中" + count + "辆车型");
        return count;
    }

    /**
     * 向延保刷记录表中刷入车辆车型,车辆 ID
     *
     * @throws Exception
     */
    @Override
    @MethodComment(name = "延保类型根据类型中文写入类型ID")
    public Integer updateExCarTypeQuartz() throws Exception {
        logger.info("系统于" + DateUtils.getTimeStr() + "刷新延保记录中延保类别");
        String sql = "update t_crm_car_exsurance " +
                " set exsurance_company_id= CASE WHEN LOCATE('润华', exsurance_company_name)  THEN 1   " +
                " WHEN LOCATE('集团', exsurance_company_name) THEN 1 " +
                " WHEN LOCATE('自主', exsurance_company_name) THEN 1 " +
                " WHEN LOCATE('厂', exsurance_company_name) THEN 2 " +
                " WHEN LOCATE('通用', exsurance_company_name) THEN 2 " +
                " WHEN LOCATE('大众', exsurance_company_name) THEN 2 " +
                " ELSE 3 END  ";

        int count = this.carDao.executeSQL(sql);
        logger.info("系统于" + DateUtils.getTimeStr() + "刷新延保记录中" + count + "辆车型");
        return count;
    }

    /**
     * 向续保记录刷记录表中刷入车辆车型,车辆 ID，车架号、车牌号
     *
     * @throws Exception
     */
    @Override
    @MethodComment(name = "刷新续保记录车辆ID及车型、联系人电话")
    public Integer updateInCarInfoQuartz() throws Exception {
        logger.info("系统于" + DateUtils.getTimeStr() + "刷新续保记录中车型");

        //通过车牌号关联任务
        String sqlPlate = "UPDATE t_crm_car_insurance i INNER JOIN t_crm_car c ON i.company_id = c.company_id" +
                " AND i.plate_num = c.plate_num and (i.user_carid is null or i.user_carid='') SET " +
                " i.vin = c.vin, i.user_carid = c.id, i.car_series = c.series";
        int count = this.carDao.executeSQL(sqlPlate);
        //续保通过车架号关联信息
        String sqlVin = "UPDATE t_crm_car_insurance i INNER JOIN t_crm_car c ON i.company_id = c.company_id" +
                " AND i.vin = c.vin   and  (i.user_carid is null or i.user_carid='')  SET i.plate_num = c.plate_num," +
                " i.vin = c.vin, i.user_carid = c.id, i.car_series = c.series";
        count += this.carDao.executeSQL(sqlVin);

        String sqlByNamePlate = "UPDATE t_crm_car_insurance i INNER JOIN t_crm_car c ON i.company_id = c.company_id" +
                " AND i.plate_num = c.plate_num  SET i.user_tel=c.owner_tel where i.user_tel is null " +
                "and c.plate_num not in ('无牌照','新车') and c.plate_num is not null";
        count += this.carDao.executeSQL(sqlByNamePlate);

        String sqlByNameVin = "UPDATE t_crm_car_insurance i INNER JOIN t_crm_car c ON i.company_id = c.company_id" +
                " AND  i.vin = c.vin SET i.user_tel=c.owner_tel where i.user_tel is null " +
                "and c.plate_num not in ('无牌照','新车') and c.plate_num is not null";
        count += this.carDao.executeSQL(sqlByNameVin);

        logger.info("系统于" + DateUtils.getTimeStr() + "刷新续保记录中" + count + "辆续保信息");
        return count;
    }

    /******
     * 刷新延保起始里程
     * @return
     * @throws Exception
     */
    @Override
    @MethodComment(name = "刷新延保起始里程")
    public Integer updateExCarMileQuartz() throws Exception {
        logger.info("系统于" + DateUtils.getTimeStr() + "刷新续保记录中起始里程");
        String sql = "update t_crm_car_exsurance e set e.start_legend =  100000 ,e.stop_legend = 100000 + (FROM_UNIXTIME(e.restop_insurance/1000,'%Y') - FROM_UNIXTIME(e.restart_insurance/1000,'%Y') ) *20000 " +
                "where e.stop_legend is null";
        int count = this.carDao.executeSQL(sql);
        logger.info("系统于" + DateUtils.getTimeStr() + "刷新续保记录中" + count + "刷新续保记录中起始里程");
        return count;
    }


    /**
     * 计算车辆行驶里程
     *
     * @throws Exception
     */
    @Override
    @MethodComment(name = "计算车辆行驶里程，更新车辆上次维修时间")
    public Integer updateRunMileQuartz() throws Exception {
        logger.info(DateUtils.getTimeStr() + "刷新车辆行驶里程");
        String sql = "update t_crm_car c inner join (select o.car_id,max(o.run_mile*1) as run_mile,MAX(balance_date) balance_date from" +
                " t_crm_car_workorder o  group by o.car_id) temp on c.id = temp.car_id set c.run_mile = temp.run_mile," +
                "c.prev_maintain_date=temp.balance_date,c.last_maintain_date=temp.balance_date,c.maintain_start_mile = temp.run_mile ";
        int count = this.carDao.executeSQL(sql);
        String sqlReal = "update t_crm_car c inner join (select o.car_id,max(o.run_mile*1) as run_mile,MAX(balance_date) balance_date from" +
                " t_crm_car_workorder o where o.real_amount>'0.00' group by o.car_id) temp on c.id = temp.car_id set " +
                "c.prev_maintain_date=temp.balance_date,c.last_maintain_date=temp.balance_date ";
        count += this.carDao.executeSQL(sqlReal);
        logger.info("刷新行驶里程车辆:" + count + "辆");
        return count;
    }

    //修正续保记录状态

    /**
     * 回写续保类型编码
     *
     * @throws Exception
     */
    @Override
    @MethodComment(name = "刷新续保类型")
    public Integer updateInTypeQuartz() throws Exception {
        String upSQL = "update t_crm_car_insurance ix " +
                "   set ix.type= case when(locate('新', ix.base_type) > 0 AND locate('续', ix.base_type) <= 0) then 1 " +
                " when (locate('新', ix.base_type)> 0 and locate('续', ix.base_type)> 0) then 2 " +
                " when (LOCATE('续保',ix.base_type) > 0) then 3 " +
                " when ix.base_type= '转保续' then 4 " +
                " WHEN ix.base_type= '活跃' then 6 " +
                " WHEN ix.base_type= '非活跃' then 7 " +
                "else 5 end " +
                " where ix.type is null";
        logger.info(DateUtils.getTimeStr() + "刷新续保类型");
        int count = this.carDao.executeSQL(upSQL);
        logger.info("刷新续保记录数:" + count + "条");
        return count;

    }

    /***
     * 大众车型工单同步
     */
//    @Override
//    @MethodComment(name = "大众根据工单同步车系")
//    public Integer syncDasAutoSeriesQuartz() throws Exception {
//        String sql = "update t_crm_car c inner JOIN t_crm_car_workorder o on c.id = o.car_id and o.company_id = ?  set c.series = o.series ";
//        return this.carDao.executeSQL(sql, "8a1029015b612d40015b6142c2a50470");
//    }

}
